<?php

include_once('ConnectionManager.php');

function loadProducts($keyword, $categoryID, $start_position, $row_per_page) {
    $query = "SELECT * FROM PRODUCT";
    if (isset($categoryID) && $categoryID != "") {
        $query .= "  WHERE CAT_ID = '$categoryID'";
    }

    if (isset($keyword)) {
        if (isset($categoryID) && $categoryID != "") {
            $query .= " AND";
        } else {
            $query .= " WHERE";
        }
        $query .= " PRO_NAME LIKE " . "'%" . $keyword . "%' AND PRO_STATUS <> 6";
    }
    $query .= " ORDER BY PRO_ID DESC LIMIT " . $start_position . ", " . $row_per_page;
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function loadProductByCateAndStatus($status, $categoryID, $start_position, $row_per_page) {
    $query = "SELECT * FROM PRODUCT";
    if (isset($categoryID) && $categoryID != "") {
        $query .= " WHERE CAT_ID = '$categoryID' AND PRO_STATUS <> 6";
    } else if (isset($status) && $status != "") {
        $query .= " WHERE PRO_STATUS = $status";
    }
    $query .= " ORDER BY PRO_ID DESC LIMIT " . $start_position . ", " . $row_per_page;
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function countNumberOfResult($keyword, $categoryID) {
    $query = "SELECT * FROM PRODUCT";
    if (isset($categoryID) && $categoryID != "") {
        $query .= "  WHERE CAT_ID = '$categoryID'";
    }

    if (isset($keyword)) {
        if (isset($categoryID) && $categoryID != "") {
            $query .= " AND";
        } else {
            $query .= " WHERE";
        }
        $query .= " PRO_NAME LIKE " . "'%" . $keyword . "%'";
    }

    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->getNumRows();
}

function coutNumberOfResultByStatusAndCate($status, $categoryID) {
    $query = "SELECT * FROM PRODUCT";
    if (isset($categoryID) && $categoryID != "") {
        $query .= "  WHERE CAT_ID = '$categoryID'";
    } else if (isset($status) && $status != "") {
        $query .= "  WHERE PRO_STATUS = $status";
    }

    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->getNumRows();
}

function loadProductByID($productID) {
    $query = "SELECT * FROM PRODUCT WHERE PRO_ID = " . $productID;
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->fetchArray();
}

function loadAvailableRelatedProductByID($proID) {
    $query = "SELECT * FROM PRODUCT ";
    if ($proID != "") {
        $query = $query . "WHERE PRO_ID <> " . $proID . " AND PRO_ID NOT IN (SELECT PRO_PRO_ID FROM RELATEDPRODUCT WHERE PRO_ID = '$proID')";
    }

    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function loadRelatedProductByID($proID) {
    $query = "SELECT * FROM PRODUCT WHERE PRO_ID IN (SELECT PRO_PRO_ID FROM RELATEDPRODUCT WHERE PRO_ID = '$proID')";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function loadAvailableRelatedProductByKeyword($keyword, $ids) {
    $query = "SELECT * FROM PRODUCT WHERE PRO_NAME LIKE" . "'%" . $keyword . "%'" . " AND PRO_ID NOT IN (" . $ids . ")";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function insertProduct($cateID, $proCode, $proName, $proPrice, $proDescription, $proStatus) {
    $query = "INSERT INTO PRODUCT (CAT_ID, PRO_CODE, PRO_NAME, PRO_PRICE, PRO_DESCRIPTION, PRO_STATUS, PRO_ADDEDDATE)
                VALUES ($cateID, '$proCode', '$proName', $proPrice, '$proDescription', $proStatus, CURDATE())";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function updateProduct($proID, $cateID, $proCode, $proName, $proPrice, $proDescription, $proStatus) {
    $query = "UPDATE PRODUCT
                SET CAT_ID = $cateID,
                    PRO_CODE = '$proCode',
                    PRO_NAME = '$proName',
                    PRO_PRICE = $proPrice,
                    PRO_DESCRIPTION = '$proDescription',
                    PRO_STATUS = $proStatus WHERE PRO_ID = $proID";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function deleteAllRelatedProduct($proID) {
    $query = "DELETE FROM RELATEDPRODUCT WHERE PRO_ID = $proID";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function insertRelatedProduct($proID, $pro_pro_id) {
    $query = "INSERT INTO RELATEDPRODUCT (PRO_ID, PRO_PRO_ID) VALUES ($proID, $pro_pro_id)";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function selectCurrentProID() {
    $query = "SELECT MAX(PRO_ID) AS MAX_ID FROM PRODUCT";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->fetchArray();
    ;
}

function deleteProductRelationship($proID) {
    $query1 = "DELETE FROM IMAGE WHERE PRO_ID = $proID";
    ConnectionManager::getInstance()->setQuery($query1);
    ConnectionManager::getInstance()->query();

    $query2 = "DELETE FROM PRODUCTDETAILVALUE WHERE PRO_ID = $proID";
    ConnectionManager::getInstance()->setQuery($query2);
    ConnectionManager::getInstance()->query();

    $query3 = "DELETE FROM DISCOUNT WHERE PRO_ID = $proID";
    ConnectionManager::getInstance()->setQuery($query3);
    ConnectionManager::getInstance()->query();

    $query4 = "DELETE FROM RELATEDPRODUCT WHERE PRO_ID = $proID OR PRO_PRO_ID = $proID";
    ConnectionManager::getInstance()->setQuery($query4);
    ConnectionManager::getInstance()->query();
}

function deleteProduct($proID) {
    $query = "DELETE FROM PRODUCT WHERE PRO_ID = $proID";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
}

function countTotalProduct() {
    $query = "SELECT PRO_ID FROM PRODUCT";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->getNumRows();
}

function loadTopProduct() {
    $query = "SELECT * FROM PRODUCT WHERE PRO_STATUS <> 6 ORDER BY PRO_ID DESC LIMIT 8";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function countNumberofProductByName($name) {
    $query = "SELECT * FROM PRODUCT WHERE PRO_NAME LIKE '%$name%' ORDER BY PRO_ID DESC";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->getNumRows();
}

function countNumProductsByCategory($catID) {
    $query = "SELECT COUNT(PRO_ID) FROM PRODUCT WHERE CAT_ID = $catID";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->fetchArray();
}

function loadProductsByNames($name, $start_position, $row_per_page) {
    $query = "SELECT * FROM PRODUCT WHERE PRO_NAME LIKE '%$name%' ORDER BY PRO_ID DESC LIMIT $start_position, $row_per_page";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function findProductByName($name) {
    $query = "SELECT PRO_ID, PRO_NAME FROM PRODUCT WHERE PRO_NAME LIKE '%" . $name . "%'";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function findProductByBanner($banID) {
    $query = "SELECT PRO_ID, PRO_NAME FROM PRODUCT WHERE PRO_ID = (SELECT PRO_ID FROM BANNER WHERE BANNER_ID = $banID)";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function loadAllProduct() {
    $query = "SELECT PRO_ID, PRO_NAME FROM PRODUCT ORDER BY PRO_NAME ASC";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

?>